from kivy.app import App
from kivy.uix.floatlayout import FloatLayout
from kivy.factory import Factory
from kivy.properties import ObjectProperty
from kivy.uix.popup import Popup
from kivy.config import Config
from kivy.core.window import Window
import win32timezone
import os, sys
from kivy.resources import resource_add_path, resource_find
import openpyxl

import re

class LoadDialog(FloatLayout):
    load = ObjectProperty(None)
    cancel = ObjectProperty(None)


class SaveDialog(FloatLayout):
    save = ObjectProperty(None)
    cancel = ObjectProperty(None)


class Root(FloatLayout):
    bom_text_input = ObjectProperty(None)
    number_text_input = ObjectProperty(None)
    def dismiss_popup(self):
        self._popup.dismiss()

    def show_bom_load(self):
        content = LoadDialog(load=self.bom_load, cancel=self.dismiss_popup)
        self._popup = Popup(title="Load file", content=content,
                            size_hint=(0.9, 0.9))
        self._popup.open()

    def show_number_load(self):
        content = LoadDialog(load=self.number_load, cancel=self.dismiss_popup)
        self._popup = Popup(title="Load file", content=content,
                            size_hint=(0.9, 0.9))
        self._popup.open()

    def show_save(self):
        content = SaveDialog(save=self.save, cancel=self.dismiss_popup)
        self._popup = Popup(title="Save file", content=content,
                            size_hint=(0.9, 0.9))
        self._popup.open()

    def bom_load(self, path, filename):
        print('===='+filename[0])
        self.bom_text_input.text = filename[0]

        self.dismiss_popup()

    def number_load(self, path, filename):
        print('===='+filename[0])
        self.number_text_input.text = filename[0]

        self.dismiss_popup()

    def save(self, path, filename):
        with open(os.path.join(path, filename), 'w') as stream:
            stream.write(self.text_input.text)
        self.dismiss_popup()

    def create_newfile(self):
        bom = openpyxl.load_workbook(self.bom_text_input.text)
        ic_book = openpyxl.load_workbook(self.number_text_input.text)
        sheet = bom['Sheet1']
        ic_sheet = ic_book['物料编码库总表FHQ)']
        # 一行一行遍历
        # for one_row in sheet.rows:
        #     for cell in one_row:
        #         print(cell, cell.value)
        sheet.insert_cols(idx=1,amount=2) #表格前面插入两个空列
        for col in sheet.iter_cols(min_row=1, min_col=6,max_col=6):  #遍历BOM文件第4列
            for cell in col:
                # print(cell.value)
                for ic_col in ic_sheet.iter_cols(min_row=1, min_col=4,max_col=4): #遍历物料表文件第4列
                    for ic_cell in ic_col:
                        #print(ic_cell.value)
                        if not ic_cell.value is None:
                            # print(cell.value+'---'+ic_cell.value)
                            if cell.value.lower().strip() == ic_cell.value.lower().strip():    #对比两列内容
                                print(cell.value+'---'+ic_sheet['A'+str(ic_cell.row)].value)
                                sheet['A'+str(cell.row)] = ic_sheet['A'+str(ic_cell.row)].value
                                sheet['B'+str(cell.row)] = ic_sheet['B'+str(ic_cell.row)].value

        for col in sheet.iter_cols(min_row=1, min_col=5,max_col=5):
            for cell in col:
                values = cell.value.split(" ")
                new_values = ""
                for x in values:
                    matchObj  = re.match(r'^([A-Z]+)([0-9]+)-([0-9]+)$',x,re.I)
                    if not matchObj is None:
                        # print ("matchObj.group() : "+matchObj.group())
                        # print ("matchObj.group(1) : "+matchObj.group(1))
                        # print ("matchObj.group(2) : "+matchObj.group(2))
                        # print ("matchObj.group(3) : "+matchObj.group(3))
                        for num in range(int(matchObj.group(2)),int(matchObj.group(3))+1):
                            print (matchObj.group(1)+str(num))
                            new_values += matchObj.group(1)+str(num)+" "
                    else:
                        new_values += x+" "
                cell.value = new_values
        sheet.insert_cols(idx=3,amount=2) #表格前面插入两个空列
        for col in sheet.iter_cols(min_row=1, min_col=8,max_col=8):
            for cell in col:
                sheet['C'+str(cell.row)] = cell.value
        for col in sheet.iter_cols(min_row=1, min_col=7,max_col=7):
            for cell in col:
                sheet['D'+str(cell.row)] = cell.value
        sheet.delete_cols(idx=5,amount=1)
        sheet.delete_cols(idx=6,amount=2)
        sheet.column_dimensions['A'].width=20
        sheet.column_dimensions['B'].width=20
        sheet.column_dimensions['E'].width=5
        bom.save('.\BOM_new.xlsx'); #另存为新文件
        bom.close()
    

class EditorApp(App):
    pass


Factory.register('Root', cls=Root)
Factory.register('LoadDialog', cls=LoadDialog)
Factory.register('SaveDialog', cls=SaveDialog)


if __name__ == '__main__':
    Config.set('graphics', 'resizable', 0)
    Window.size = (900, 500)
    if hasattr(sys, '_MEIPASS'):
       resource_add_path(os.path.join(sys._MEIPASS))
    EditorApp().run()